package com.ph.irp.base.accountbook.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.accountbook.dao.PBaseAccountbookDAO;
import com.ph.irp.model.PBaseAccountbook;
import com.ph.util.DBUtil;

public class PBaseAccountbookDAOImpl implements PBaseAccountbookDAO {
	// dataSource is a tool that we use to connect the database
	private ComboPooledDataSource dataSource;

	private List<PBaseAccountbook> accountList;

	private PBaseAccountbook pbaseAccountbook = null;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * Method getAllAccountbooks used to query all accountbooks we have stored
	 * in database
	 */
	@Override
	public List<PBaseAccountbook> getAllAccountbooks()throws Throwable {
		// TODO Auto-generated method stub
		accountList = new ArrayList<PBaseAccountbook>();
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// Store the results we get from the database
		ResultSet resultSet = null;

		try {
			// 构建SQL语句
			StringBuilder sb = new StringBuilder();
			sb.append("select a.accountbookCode ,a.accountbookName ,");
			sb.append("a.accountbookTypeId ,a.shortName ,");
			sb.append("a.banlanceDirection ,a.foreignCurrency ,");
			sb.append("a.endExchange ,a.dealingCheck ,");
			sb.append("a.amountCashCheck ,a.measureUnitId ,");
			sb.append("a.cashAccountBook ,a.bankAccountBook,");
			sb.append("a.outDate ,a.cashEquivalent ,a.accountbookId ");
			sb.append("from p_base_accountbook a");

			// 获得数据库连接����
			connection = dataSource.getConnection();
			// 由connection得到预备语句
			preparedStatement = connection.prepareStatement(sb.toString());
			// 执行sql语句
			resultSet = preparedStatement.executeQuery();
			// ���resultS����accountList
			this.turnResultSetToList(accountList, resultSet);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}// 关闭数据库连接�������
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception ex) {// 处理异常����
				// TODO Auto-generated catch block
				throw new Exception(ex.getMessage());
			}
		}
		return accountList;
	}

	/**
	 * getAccountbookById根据id查询会计科目
	 */
	@Override
	public PBaseAccountbook getAccountbookById(int id) throws Throwable{
		accountList = new ArrayList<PBaseAccountbook>();
		pbaseAccountbook = new PBaseAccountbook();
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// Store the results we get from the database
		ResultSet resultSet = null;

		// 根据Id查询会计科目�����sql
		StringBuilder sb = new StringBuilder();
		sb.append("select a.accountbookCode,a.accountbookName,");
		sb.append("a.accountbookTypeId,a.shortName,");
		sb.append("a.banlanceDirection,a.foreignCurrency,");
		sb.append("a.endExchange,a.dealingCheck,");
		sb.append("a.amountCashCheck,a.measureUnitId,");
		sb.append("a.cashAccountBook,a.bankAccountBook,");
		sb.append("a.outDate,a.cashEquivalent,a.accountbookId ");
		sb.append("from p_base_accountbook a where a.accountbookId =?");

		try {
			// 获得数据库连接
			connection = dataSource.getConnection();
			// 由数据库连接得到预备语句������
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, id);
			// 执行sql语句
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(accountList, resultSet);
			pbaseAccountbook = accountList.get(0);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return pbaseAccountbook;
	}

	/**
	 * getAccountbookByCode根据代码查询会计科目
	 */
	@Override
	public List<PBaseAccountbook> getAccountbookByCode(String accountbookCode) throws Throwable{
		// TODO Auto-generated method stub
		accountList = new ArrayList<PBaseAccountbook>();
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// Store the results we get from the database
		ResultSet resultSet = null;

		StringBuilder sb = new StringBuilder();
		sb.append("select a.accountbookCode,a.accountbookName,");
		sb.append("a.accountbookTypeId,a.shortName,");
		sb.append("a.banlanceDirection,a.foreignCurrency,");
		sb.append("a.endExchange,a.dealingCheck,");
		sb.append("a.amountCashCheck,a.measureUnitId,");
		sb.append("a.cashAccountBook,a.bankAccountBook,");
		sb.append("a.outDate,a.cashEquivalent,a.accountbookId ");
		sb.append("from p_base_accountbook a where a.accountbookCode like ?");
		try {
			// 获得数据库连接
			connection = dataSource.getConnection();
			// 由数据库连接得到预备语句������
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%" + accountbookCode + "%");
			// 执行sql语句
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(accountList, resultSet);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return accountList;
	}

	/**
	 * getAccountbookByNameAndCode根据名称和代码查询会计科目
	 */
	@Override
	public List<PBaseAccountbook> getAccountbookByNameAndCode(
			String accountbookName, String accountbookCode) throws Throwable{
		accountList = new ArrayList<PBaseAccountbook>();
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// Store the results we get from the database
		ResultSet resultSet = null;

		StringBuilder sb = new StringBuilder();
		sb.append("select a.accountbookCode  ,a.accountbookName ,");
		sb.append("a.accountbookTypeId ,a.shortName ,");
		sb.append("a.banlanceDirection ,a.foreignCurrency ,");
		sb.append("a.endExchange ,a.dealingCheck ,");
		sb.append("a.amountCashCheck ,a.measureUnitId ,");
		sb.append("a.cashAccountBook ,a.bankAccountBook,");
		sb.append("a.outDate ,a.cashEquivalent ,a.accountbookId ");
		sb
				.append("from p_base_accountbook a where a.accountbookName=? and a.accountbookCode=?");
		try {
			// 得到数据库连接
			connection = dataSource.getConnection();

			// 由数据库连接得到预备语句������
			preparedStatement = connection.prepareStatement(sb.toString());

			preparedStatement.setString(1, accountbookName);
			preparedStatement.setString(2, accountbookCode);
			// 执行sql语句
			resultSet = preparedStatement.executeQuery();

			this.turnResultSetToList(accountList, resultSet);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

		return accountList;
	}

	/**
	 * getAccountbookByName根据名称查询会计科目
	 */
	@Override
	public List<PBaseAccountbook> getAccountbookByName(String accountbookName) throws Throwable{
		// TODO Auto-generated method stub

		accountList = new ArrayList<PBaseAccountbook>();
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// Store the results we get from the database
		ResultSet resultSet = null;

		// 根据name查询会计科目�����sql

		StringBuilder sb = new StringBuilder();
		sb.append("select a.accountbookCode  ,a.accountbookName ,");
		sb.append("a.accountbookTypeId ,a.shortName ,");
		sb.append("a.banlanceDirection ,a.foreignCurrency ,");
		sb.append("a.endExchange ,a.dealingCheck ,");
		sb.append("a.amountCashCheck ,a.measureUnitId ,");
		sb.append("a.cashAccountBook ,a.bankAccountBook,");
		sb.append("a.outDate ,a.cashEquivalent ,a.accountbookId ");
		sb.append("from p_base_accountbook a where a.accountbookName like ?");
		try {
			// 获得数据库连接
			connection = dataSource.getConnection();
			// 由数据库连接得到预备语句������
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%" + accountbookName + "%");
			// 执行查询，得到结果集��
			resultSet = preparedStatement.executeQuery();
			// 将resultSet封装到accountList
			this.turnResultSetToList(accountList, resultSet);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}

		return accountList;
	}

	/**
	 * deleteAccountbookById根据会计科目id来删除会计科目 parameter accountbookId can not be
	 * null
	 */
	@Override
	public int deleteAccountbookById(Integer accountbookId) throws Throwable{
		// TODO Auto-generated method stub
		int result = 0;
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;
		// 根据Id删除会计科目�����sql

		StringBuilder sb = new StringBuilder();
		sb.append("delete from p_base_accountbook where accountbookId=?");

		try {
			connection = this.dataSource.getConnection();

			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, accountbookId);
			result = preparedStatement.executeUpdate();

		} catch (Exception e) {// 处理异常����
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放数据库连接资源��

			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {// 处理异常�
				// TODO Auto-generated catch block
				throw new Exception(ex.getMessage());
			}

		}

		return result;
	}

	/**
	 * 修改会计科目 parameter pbaseAccountbook can not be null
	 */
	public int update(PBaseAccountbook pbaseAccountbook) throws Throwable{
		int result = 0;

		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// 根据id修改会计科目

		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_accountbook set accountbookCode=? ,");
		sb.append("accountbookName=? ,accountbookTypeId=? ,");
		sb.append("shortName=?,banlanceDirection=? ,foreignCurrency=?,");
		sb.append("endExchange=?,dealingCheck=?,amountCashCheck=?,");
		sb.append("measureUnitId=?,cashAccountBook=?,bankAccountBook=?,");
		sb.append("outDate=?,cashEquivalent=? where accountbookId=?");

		try {
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());

			// 给宿主变量绑定值
			preparedStatement.setString(1, pbaseAccountbook
					.getAccountbookCode());
			preparedStatement.setString(2, pbaseAccountbook
					.getAccountbookName());
			preparedStatement
					.setInt(3, pbaseAccountbook.getAccountbookTypeId());
			preparedStatement.setString(4, pbaseAccountbook.getShortName());
			preparedStatement
					.setInt(5, pbaseAccountbook.getBanlanceDirection());
			preparedStatement.setInt(6, pbaseAccountbook.getForeignCurrency());
			preparedStatement.setInt(7, pbaseAccountbook.getEndExchange());
			preparedStatement.setInt(8, pbaseAccountbook.getDealingCheck());
			preparedStatement.setInt(9, pbaseAccountbook.getAmountCashCheck());
			preparedStatement.setInt(10, pbaseAccountbook.getMeasureUnitId());
			preparedStatement.setInt(11, pbaseAccountbook.getCashAccountBook());
			preparedStatement.setInt(12, pbaseAccountbook.getBankAccountBook());
			preparedStatement.setInt(13, pbaseAccountbook.getOutDate());
			preparedStatement.setInt(14, pbaseAccountbook.getCashEquivalent());
			preparedStatement.setInt(15, pbaseAccountbook.getAccountbookId());

			// 执行sql语句，返回影响的行数
			result = preparedStatement.executeUpdate();

		} catch (Exception e) {// 处理异常����
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放数据库连接资源��

			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {// 处理异常�
				// TODO Auto-generated catch block
				throw new Exception(ex.getMessage());
			}

		}

		return result;
	}

	// 将结果集resultSet封装到 <PBaseAccountbook>
	public List<PBaseAccountbook> turnResultSetToList(
			List<PBaseAccountbook> accountList, ResultSet resultSet)
			throws Throwable{

		while (resultSet.next()) {
			// 声明PBaseAccountbook 类型变量
			PBaseAccountbook accountbook = new PBaseAccountbook();

			// 把resultSet中的每一条记录封装到accountbook中
			accountbook.setAccountbookCode(resultSet
					.getString("accountbookCode"));
			accountbook.setAccountbookName(resultSet
					.getString("accountbookName"));
			accountbook.setAccountbookTypeId(resultSet
					.getInt("accountbookTypeId"));
			accountbook.setShortName(resultSet.getString("shortName"));
			accountbook.setBanlanceDirection(resultSet
					.getInt("banlanceDirection"));
			accountbook.setForeignCurrency(resultSet.getInt("foreignCurrency"));
			accountbook.setEndExchange(resultSet.getInt("endExchange"));
			accountbook.setDealingCheck(resultSet.getInt("dealingCheck"));
			accountbook.setAmountCashCheck(resultSet.getInt("amountCashCheck"));
			accountbook.setMeasureUnitId(resultSet.getInt("measureUnitId"));
			accountbook.setCashAccountBook(resultSet.getInt("cashAccountBook"));
			accountbook.setBankAccountBook(resultSet.getInt("bankAccountBook"));
			accountbook.setOutDate(resultSet.getInt("outDate"));
			accountbook.setCashEquivalent(resultSet.getInt("cashEquivalent"));
			accountbook.setAccountbookId(resultSet.getInt("accountbookId"));

			// add accountbookModel to accountList
			accountList.add(accountbook);

		}// end while
		return accountList;
	}

	/**
	 * addAccountbook 新增会计科目
	 */
	@Override
	public int addAccountbook(PBaseAccountbook pbaseAccountbook) throws Throwable{
		// TODO Auto-generated method stub
		int result = 0;
		// connection to database ,to create Statement for us
		Connection connection = null;

		// Statement created by connection to execute the SQL
		PreparedStatement preparedStatement = null;

		// 新增会计科目sql
		StringBuilder sb = new StringBuilder();
		sb
				.append("insert into p_base_accountbook(accountbookCode,accountbookName,");
		sb.append("accountbookTypeId,shortName,banlanceDirection,");
		sb.append("foreignCurrency,endExchange,dealingCheck,amountCashCheck,");
		sb.append("measureUnitId,cashAccountBook,bankAccountBook,outDate,");
		sb
				.append("cashEquivalent,parentAccountId) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

		try {
			// 从数据源获得数据库连接
			connection = this.dataSource.getConnection();
			// 由connection得到预备语句
			preparedStatement = connection.prepareStatement(sb.toString());
			// 给宿主变量绑定值
			preparedStatement.setString(1, pbaseAccountbook
					.getAccountbookCode());
			preparedStatement.setString(2, pbaseAccountbook
					.getAccountbookName());
			preparedStatement
					.setInt(3, pbaseAccountbook.getAccountbookTypeId());
			preparedStatement.setString(4, pbaseAccountbook.getShortName());
			preparedStatement
					.setInt(5, pbaseAccountbook.getBanlanceDirection());
			preparedStatement.setInt(6, pbaseAccountbook.getForeignCurrency());
			preparedStatement.setInt(7, pbaseAccountbook.getEndExchange());
			preparedStatement.setInt(8, pbaseAccountbook.getDealingCheck());
			preparedStatement.setInt(9, pbaseAccountbook.getAmountCashCheck());
			preparedStatement.setInt(10, pbaseAccountbook.getMeasureUnitId());
			preparedStatement.setInt(11, pbaseAccountbook.getCashAccountBook());
			preparedStatement.setInt(12, pbaseAccountbook.getBankAccountBook());
			preparedStatement.setInt(13, pbaseAccountbook.getOutDate());
			preparedStatement.setInt(14, pbaseAccountbook.getCashEquivalent());
			preparedStatement.setInt(15, pbaseAccountbook.getParentAccountId());

			result = preparedStatement.executeUpdate();
		} catch (Exception e) {// 捕捉异常，做相应处理����
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 释放数据库连接资源��
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {// 处理异常���
				// TODO Auto-generated catch block
				throw new Exception(ex.getMessage());
			}
		}
		return result;
	}
}
